package com.lanou.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DBUtils {
	
	static Properties p = new Properties();
	
	static DataSource ds = null;
	
	static {
		try {
			
			ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
			
			p.load(classLoader.getResourceAsStream("db.properties"));
			ds = BasicDataSourceFactory.createDataSource(p);
			//Class.forName(p.getProperty("driver"));
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static Connection getConnection() throws Exception {
		return ds.getConnection();
	}
	
	public static int update(String sql,Object... args) {		
		int row = 0;
		try(
				Connection conn = getConnection();
				PreparedStatement ps = conn.prepareStatement(sql);
			){
			for (int i = 0;i < args.length; i++) {
				ps.setObject(i+1, args[i]);
			}
			row = ps.executeUpdate();	
		} catch (Exception e) {
			e.printStackTrace();
		}
		return row;
	}
	public static int update(Connection conn,String sql,Object... args) {		
		int row = 0;
		try(
				PreparedStatement ps = conn.prepareStatement(sql);
				){
			for (int i = 0;i < args.length; i++) {
				ps.setObject(i+1, args[i]);
			}
			row = ps.executeUpdate();	
		} catch (Exception e) {
			e.printStackTrace();
		}
		return row;
	}
	
	
	public static <T> T queryOne(String sql,Class<T> clazz,Object... args) {
		List<T> list = query(sql,clazz,args);
		return list.size() == 0? null : list.get(0); 
	}
	
	public static <T> List<T> query(String sql,Class<T> clazz,Object... args){
		List<T> list = new ArrayList<>();
		try(Connection conn = DBUtils.getConnection();
				PreparedStatement ps = conn.prepareStatement(sql);
				){
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1, args[i]);
			}
			ResultSet rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while(rs.next()) {				
				T t = clazz.newInstance();
				for(int i = 0; i < columnCount; i++) {
					String fieldName = rsmd.getColumnLabel(i+1);
					Field field = clazz.getDeclaredField(fieldName);
					field.setAccessible(true);
					Object value = rs.getObject(fieldName);
					if(value != null) {
						field.set(t, value);
					}
				}
				list.add(t);
			}	
		} catch (Exception e) {
			e.printStackTrace();
		}	
		return list;
	}
	
	public static <T> T queryOne(Connection conn,String sql,Class<T> clazz,Object... args) {
		List<T> list = query(conn,sql,clazz,args);
		return list.size() == 0? null : list.get(0); 
	}
	public static <T> List<T> query(Connection conn,String sql,Class<T> clazz,Object... args){
		List<T> list = new ArrayList<>();
		try(
				PreparedStatement ps = conn.prepareStatement(sql);
				){
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1, args[i]);
			}
			ResultSet rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while(rs.next()) {				
				T t = clazz.newInstance();
				for(int i = 0; i < columnCount; i++) {
					String fieldName = rsmd.getColumnLabel(i+1);
					Field field = clazz.getDeclaredField(fieldName);
					field.setAccessible(true);
					Object value = rs.getObject(fieldName);
					if(value != null) {
						field.set(t, value);
					}
				}
				list.add(t);
			}	
		} catch (Exception e) {
			e.printStackTrace();
		}	
		return list;
	}
}
